﻿Imports System.Data
Imports System.IO
Imports log4net
Imports ISNet.WebUI.WebGrid
Imports Microsoft.Office.Interop.Excel

Partial Class ImportProjectFrm
    Inherits System.Web.UI.Page

    Public AlertText As String
    Private Shared logger As ILog = LogManager.GetLogger("ImportProjectFrm")

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Session("users") Is Nothing Then
            Response.Redirect("LoginForm.aspx")
        End If
        If IsPostBack = False Then
            PlanYearsTxt.Text = Session("PlanYear")
            Label1.Text = "ระบบจัดทำงบประมาณ > จัดทำงบประมาณ > นำเข้า สรุปงานโครงการและงานสนับสนุน"
            txtVersion.Text = "1"
        End If
        Server.ScriptTimeout = 3600
    End Sub
    Protected Sub btnImportAll_Click(sender As Object, e As ImageClickEventArgs) Handles btnImportAll.Click
        Dim bd_adapter As New MassChangeDSTableAdapters.BudgetDetailsTableAdapter
        Dim bd12_adapter As New MassChangeDSTableAdapters.Budget12TableAdapter
        Dim ex_adapter As New ExportTableAdapters.BudgetDetailsTableAdapter
        Dim pro_adapter As New BudgetDetailTableAdapters.BudgetDetailProjectTableAdapter
        Dim Extension As String = ""
        Dim FileName As String = ""
        Dim error_txt As String = ""
        Dim TotalAmount As Decimal
        If FileUpload1.HasFile Then
            Try
                'Check จำนวน record ของ Project
                Dim countPro As BudgetDetail.BudgetDetailProjectDataTable
                countPro = pro_adapter.GetDataCountProject(Val(PlanYearsTxt.Text), txtVersion.Text)
                
                If countPro.Rows.Count > 0 Then
                    If Val(countPro.Rows(0)("numrow").ToString()) > 0 Then
                        Throw New Exception("")
                    End If
                Else
                    Throw New Exception("")
                End If
                'หาค่า Sum
                Dim sumPro As BudgetDetail.BudgetDetailProjectDataTable
                sumPro = pro_adapter.GetDataBySumAmount(Val(PlanYearsTxt.Text), txtVersion.Text)
                If sumPro.Rows.Count > 0 Then
                    TotalAmount = Val(sumPro.Rows(0)("sumamount").ToString())
                End If
            Catch ex As Exception
            Alert("ท่านยังไม่ได้ลบข้อมูลของปีงบประมาณ " & PlanYearsTxt.Text & " เวอร์ชั่น " & txtVersion.Text & " กรุณาลบข้อมูลก่อนนำเข้าข้อมูล")
            Exit Sub
        End Try

            FileUpload1.SaveAs(Server.MapPath("~\Import\") & FileUpload1.FileName)
        Else
            Alert("กรุณาระบุไฟล์")
            Exit Sub
        End If

        Dim strconn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &
         Server.MapPath("~\Import\") & FileUpload1.FileName & ";Extended Properties=""Excel 12.0 Xml;HDR=NO"";"

        Dim conn As New System.Data.OleDb.OleDbConnection(strconn)
        conn.Open()

        Dim dtSheets As System.Data.DataTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)
        Dim listSheet As New List(Of String)
        Dim drSheet As DataRow

        For Each drSheet In dtSheets.Rows
            listSheet.Add(drSheet("TABLE_NAME").ToString())
        Next
        Dim cmd As System.Data.OleDb.OleDbDataAdapter
        Dim dt As New System.Data.DataTable
        Dim dataset As New DataSet()

        For Each sheet As String In listSheet
            cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [" & sheet & "] ", conn)
            cmd.Fill(dataset)
        Next
        conn.Close()

        Try
            File.Delete(Server.MapPath("~\Import\") & FileUpload1.FileName)
        Catch ex As Exception
        End Try

        Dim oTrans = bd_adapter.CreateTransaction()
        Dim linecount As Integer = 0
        Dim sum As Decimal = 0
        Dim projectCode As String
        Dim fund As String
        Dim planyaer As Integer
        Dim versionf As String
        Dim fundcenterCode As String
        Dim shortDesc As String
        Dim desc As String
        Dim amount As Decimal
        For Each dr As DataRow In dataset.Tables(0).Rows
            If dr(0).ToString() = "H" Then 'Header
                planyaer = Val(dr(3).ToString())
                versionf = dr(4).ToString()
            ElseIf dr(0).ToString() = "D" Then 'Detail
                linecount += 1
                projectCode = dr(2).ToString()
                fund = projectCode.Substring(0, 1)
                fundcenterCode = dr(5).ToString()
                shortDesc = dr(3).ToString()
                desc = dr(4).ToString()
                amount = Val(dr(6).ToString())

                If shortDesc.Length > 20 Then 'ตรวจสอบชื่อย่อ 20 digits
                    error_txt = error_txt & "พบปัญหาบรรทัดที่ " & dr(1).ToString() & " ชื่อย่อโปรเจค " & projectCode & " ยาวมากกว่า 20 digits" & vbCrLf
                    logger.Error(error_txt)
                ElseIf desc.Length > 40 Then 'ตรวจสอบชื่อเต็ม 40 digits
                    error_txt = error_txt & "พบปัญหาบรรทัดที่ " & dr(1).ToString() & " ชื่อเต็มโปรเจค " & projectCode & " ยาวมากกว่า 40 digits" & vbCrLf
                    logger.Error(error_txt)
                Else 'Insert table FundedProgram
                    InsertFundedProgram(fund, projectCode, planyaer, versionf, fundcenterCode, shortDesc, desc, amount, "0")
                End If
            ElseIf dr(0).ToString() = "T" Then 'Total
                sum = Val(dr(3).ToString())
                If sum <> TotalAmount Then
                    error_txt = error_txt & "พบปัญหาบรรทัดที่ " & dr(1).ToString() & " จำนวนเงินรวมมีค่าไม่เท่ากับค่าที่มีอยู่ " & vbCrLf
                    logger.Error(error_txt)
                End If
            End If
        Next
        Try
            If error_txt <> "" Then
                hd_err_txt.Value = error_txt
                Button1.Visible = True
                Throw New Exception("")
            Else
                Button1.Visible = False
                oTrans.Commit()
                Alert("เสร็จสิ้นการนำเข้าข้อมูล จำนวนรายการทั้งหมดคือ " & linecount & " รายการ")
            End If
        Catch ex As Exception
            oTrans.Rollback()
            logger.Error(ex.Message)
            logger.Error(ex.StackTrace)
            Dim err As String = ex.Message.ToString().Replace("""", "'").Replace("'", "").Replace(Chr(13), "").Replace(",", "").Replace("/", "").Replace("<br>", "\\n").Replace("\\n", "").Replace(System.Environment.NewLine, "").Replace("\r", "").Replace(vbCr, "").Replace(vbLf, "")
            Alert("มีข้อผิดพลาดในการนำเข้าข้อมูล" + "\r\n" + err)
        End Try
    End Sub
    Sub InsertFundedProgram(Fund As String, ProjectCode As String, planyear As Decimal, version As String, FundCenterCode As String, ShortDescription As String, Description As String, Amount As Decimal, DuringFlag As Integer)
        Dim bd_adapter As New MassChangeDSTableAdapters.BudgetDetailsTableAdapter
        Dim sql As String
        sql = "insert into FundedProgram (Fund , Project_ProjectCode, Activity_Level1, Activity_Level2, Activity_Level3, PlanYear,"
        sql += " Version, FundCenterCode, ShortDescription, Description, Amount, CreateDate"
        sql += " CreateBy, DuringFlag) "
        sql += " values ('" & Fund & "','" & ProjectCode & "', '00', '00', '000', " & planyear & ", "
        sql += " '" & version & "', '" & ShortDescription & "', '" & Description & "', " & Amount & ", getdate(), "
        sql += " " & Session("users") & ", " & DuringFlag & ") "
        bd_adapter.Execute(sql)
    End Sub
    Function GenerateFileName(ByVal data As String) As String
        Return Now().ToString(data)
    End Function
    Sub Alert(ByVal message As String)
        Try
            AlertText += "<script> alert(""" & message & """);</script>"
        Catch ex As Exception
        End Try
    End Sub
    'Private Function chkFileName(filename As String) As Boolean
    '    If WebCombo1.Value = "1" Then
    '        Dim rgx As New Regex("^(ZFMI_)+\d{3}(BB_)+(" & PlanYearsTxt.Text & ")")
    '        Return rgx.IsMatch(filename)
    '    ElseIf WebCombo1.Value = "2" Then
    '        Dim rgx As New Regex("^(Interface_Non FM to )" + "(BB_" & PlanYearsTxt.Text & ")")
    '        Return rgx.IsMatch(filename)
    '    ElseIf WebCombo1.Value = "3" Then
    '        Dim rgx As New Regex("^(ZFMI_)+\d{3}(BB_)+(" & PlanYearsTxt.Text & ")")
    '        Return rgx.IsMatch(filename)
    '    ElseIf WebCombo1.Value = "4" Then
    '        Dim rgx As New Regex("^(HR)+(BB_)+(" & PlanYearsTxt.Text & ")")
    '        Return rgx.IsMatch(filename)
    '    Else
    '        Return False
    '    End If
    'End Function
    Public Function SpacePrefix(strParam As String, nLen As Integer) As String
        Dim tmp As String
        Dim k As Integer
        tmp = LTrim(RTrim(strParam))
        If Len(tmp) < nLen Then
            For k = 1 To nLen - 1
                tmp = "0" & tmp
            Next k
        End If
        SpacePrefix = tmp
    End Function
    Private Sub downloadErr(txtlog As String)
        Dim strFileName As String = System.IO.Path.GetRandomFileName()
        Dim strName As String = "Import_Error_Log_" & strFileName & ".txt"
        Using sw As New System.IO.StreamWriter(Server.MapPath("~/Import_Error_Log/" + strName + ".txt"))
            sw.WriteLine(txtlog)
            sw.Close()
        End Using
        Response.ContentType = "text/plain"
        Dim FilePath As String = MapPath("~/Import_Error_Log/" + strName + ".txt")
        DownloadFile(strName, True, FilePath, ".txt")
        Response.End()
    End Sub
    Private Sub DownloadFile(ByVal fname As String, ByVal forceDownload As Boolean, path As String, ext As String)
        Dim type As String = ""
        Select Case ext
            Case ".htm", ".html"
                type = "text/HTML"
            Case ".txt"
                type = "text/plain"
            Case ".doc", ".rtf"
                type = "Application/msword"
            Case ".csv", ".xls"
                type = "Application/x-msexcel"
            Case Else
                type = "text/plain"
        End Select
        If (forceDownload) Then
            Response.AppendHeader("content-disposition", _
            "attachment; filename=" + fname)
        End If
        If type <> "" Then
            Response.ContentType = type
        End If
        Response.WriteFile(path)
    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        httpDownload(hd_err_txt.Value)
    End Sub
    Sub httpDownload(ByVal txtlog As String)
        Dim success As Boolean = False
        Try
            Response.Clear()
            Response.AddHeader("content-disposition", "attachment;filename=Import_log_error.txt")
            Response.Buffer = True
            Response.Charset = "UTF-8"
            Response.ContentEncoding = Encoding.Unicode
            Response.ContentType = "application/vnd.text"
            Dim stringWrite As New System.IO.StringWriter()
            Dim htmlWrite = New HtmlTextWriter(stringWrite)
            Response.Write(txtlog)
            success = True
            logger.Info("User " & Session("users") & " download file successfully.")
        Catch ex As Exception
            logger.Error(ex.Message)
            logger.Error(ex.StackTrace)
            success = False
            logger.Info("User " & Session("users") & " download file failed.")
        End Try
        Response.End()
    End Sub
    Protected Sub WebGrid1_InitializeDataSource(ByVal sender As Object, ByVal e As ISNet.WebUI.WebGrid.DataSourceEventArgs) Handles WebGrid1.InitializeDataSource
        BindGrid()
    End Sub
    Protected Sub WebGrid1_InitializePostBack(ByVal sender As Object, ByVal e As ISNet.WebUI.WebGrid.PostbackEventArgs)
        'If e.Action = PostBackAction.Custom Then
        '    Dim type_del As String = TryCast(Request("type_del"), String)
        '    Dim Deletet As String = TryCast(Request("id"), String)
        '    Dim Datalist() As String
        '    Datalist = Split(Deletet, ",")
        '    Dim opt As Integer
        '    opt = Val(Datalist(1))
        '    Dim adapter As New MasterDataSetTableAdapters.UsersTableAdapter
        '    Dim adapteruf As New MasterDataSetTableAdapters.UserFundCentersTableAdapter
        '    Dim l_adapter As New LoginDSTableAdapters.LoginLogTableAdapter
        '    If type_del = "1" Then
        '        If opt = 1 Then
        '            Dim UserName As String
        '            UserName = l_adapter.GetUserNameByID(Val(Datalist(0)))
        '            adapteruf.DeleteByUserName(UserName)
        '            adapter.DeleteUsers(Val(Datalist(0)))
        '            logger.Info("User " & Session("users") & " delete user name " & Datalist(0))
        '        Else
        '            Dim UserName As String
        '            Try
        '                UserName = l_adapter.GetUserNameByID(Val(Datalist(0)))
        '            Catch ex As Exception
        '                UserName = "xxx"
        '            End Try
        '            l_adapter.DeleteByUser(UserName)
        '            logger.Info("User " & Session("users") & " delete user name " & UserName)
        '        End If
        '    ElseIf type_del = "2" Then
        '        If opt = 1 Then
        '            For Each keyValue As String In WebGrid1.RootTable.GetCheckedRows()
        '                Dim UserName As String
        '                UserName = l_adapter.GetUserNameByID(keyValue)
        '                adapteruf.DeleteByUserName(UserName)
        '                adapter.DeleteUsers(keyValue)
        '                logger.Info("User " & Session("users") & " delete user name " & keyValue)
        '            Next keyValue
        '        Else
        '            Dim UserName As String
        '            Try
        '                UserName = l_adapter.GetUserNameByID(Val(Datalist(0)))
        '            Catch ex As Exception
        '                UserName = "xxx"
        '            End Try
        '            For Each keyValue As String In WebGrid1.RootTable.GetCheckedRows()
        '                l_adapter.DeleteByUser(keyValue)
        '                logger.Info("User " & Session("users") & " delete user name " & keyValue)
        '            Next keyValue

        '        End If
        '    End If
        '    WebGrid1.ClientAction.Refresh()
        'End If
    End Sub
    Protected Sub btnSearch_Click(sender As Object, e As ImageClickEventArgs) Handles btnSearch.Click
        BindGrid()
    End Sub
    Private Sub BindGrid()
        Dim adapter As New MasterDataSetTableAdapters.FundedProgramTableAdapter
        Dim dtTable As New MasterDataSet.FundedProgramDataTable
        Dim sql As String = " select * FROM FundedProgram "
        sql += " WHERE PLANYEAR =  " & Val(PlanYearsTxt.Text)
        sql += " AND Version =  '" & txtVersion.Text & "' "
        sql += " ORDER BY Id "
        adapter.SearchProject(dtTable, sql)
        Session("count_data") = dtTable.Rows.Count
        WebGrid1.DataSource = dtTable
    End Sub
End Class

